class Offer < ActiveRecord::Base
  belongs_to :item
	belongs_to :region
	belongs_to :solarsystem


	def self.median(id)
			@total = 0
			@offers = find(:all, :conditions => "item_id = #{id}")
			@offers.each { |o| @total = @total + o.price }
			return @total / @offers.length
	end

	def self.search(id)
			return find(:all, :conditions => "item_id = #{id}")
	end

  def self.median(id, solarsystem, sell)
    return find(:all, :conditions => "item_id = #{id} AND solarsystem_id = #{solarsystem} AND sell = #{sell} AND expires>NOW()",
                :order => "price DESC")
  end
  
  def self.compile_median
    a = Offer.median(17887, 30000142, 1)
    if a.length%2 # ungerade
      median = a[(a.length+1)/2].price
    else
      median = (a[a.length/2].price + a[(a.length/2)+1].price)/2
    end
    median
  end

  def self.meep
   # sell = Offer.calculate(:maximum , :price, :group => "item_id", :joins => "LEFT JOIN items ON items.id = item_id", 
   #                       :conditions => "solarsystem_id = 30000142 AND sell = 1 AND expires < NOW()")
    sell = ActiveRecord::Base.connection.select_all("SELECT max(price) AS price, item_id AS item_id, items.name FROM `offers`  
          LEFT JOIN items ON items.id = item_id WHERE (solarsystem_id = 30000142 AND sell = 1 AND expires > NOW())  GROUP BY item_id;")

    @data = Hash.new
    sell.each do |i|
      @data[i["item_id"]] = {:id => i["item_id"], :sell => i["price"], :name => i["name"]}
    end
    buy = ActiveRecord::Base.connection.select_all("SELECT min(price) AS price, item_id AS item_id, items.name FROM `offers`  
          LEFT JOIN items ON items.id = item_id WHERE (solarsystem_id = 30000142 AND sell = 0 AND expires > NOW())  GROUP BY item_id;")
    buy.each do |i|
      if @data[i["item_id"]] != nil
        @data[i["item_id"]][:buy] = i["price"]
      else
        @data[i["item_id"]] = {:id => i["item_id"], :buy  => i["price"], :name => i["name"]}
      end
      
    end

    @data
  end

  def self.find_hubs
    sell = Offer.count(:group => :solarsystem, :conditions => "sell = 1", :order => "count_all DESC", :limit => 10)
    buy = Offer.count(:group => :solarsystem, :conditions => "sell = 0", :order => "count_all DESC", :limit => 10)
    
  end
  
Gaps = Struct.new( :item_id, :buy, :sell, :gap)

  def self.find_gaps
    sell = Offer.find(:all, :select => "MAX(price) as price, item_id", 
                      :conditions => "solarsystem_id = 30000142 AND sell = 1", :group => :item_id, :order => :item_id)
    buy = Offer.find(:all, :select => "MIN(price) as price, item_id", 
                      :conditions => "solarsystem_id = 30000142 AND sell = 0", :group => :item_id, :order => :item_id)
    gaps = Hash.new
    sell.each do |s|
      gaps[s.item_id] = Gaps.new(s.item_id, s.price)
    end
  end

  def self.load
    logger.silence do
    #Net::IMAP.debug = true
    imap = Net::IMAP.new('imap.gmail.com', 993, true)
    #puts "new ... connectiong "
#    sql = File.new("data.sql", "w")
    
    imap.add_response_handler { |resp|
      if resp.kind_of?(Net::IMAP::UntaggedResponse) and resp.name == "EXISTS"
        puts "Mailbox now has #{resp.data} messages"
        @mails = resp.data
      end
    }
    imap.login('breedofmalakka@gmail.com', 'bom4life')
    @resp = imap.select('INBOX')
    
      @csvcount = 0
    #imap.search(['ALL']).each do |id|
    @m = imap.search(['SINCE', '29-Jan-2008'])
    puts "Processing #{@m.length} messages"
    @m.each do |id|
      @sql = []
      rep = imap.fetch(id, "BODY[HEADER.FIELDS (DATE)]")[0].attr["BODY[HEADER.FIELDS (DATE)]"].split(': ')[1]
      data = imap.fetch(id, ["ENVELOPE","BODY[TEXT]","BODYSTRUCTURE"])[0].attr["BODY[TEXT]"]
    
      csv = CSV::Reader.parse(data)
    # offers = Offer.count
    # procDump = ProcessedDumps.new(:name => File.split(file)[1])
      
      @csvcount += 1
      csv.each do |row|
        next if row[0] == "price"
        duration = row[9].split(':')[0]
        begin
          issued = Date.parse(row[8])
          reported = DateTime.parse(rep).strftime('%Y-%m-%d %H:%M:%S')
        rescue
          print "Error parsing dates: #{$!} (#{row[8]})\n"
        next
        end
        @sql.push "(#{row[4]},#{row[7]} ,#{row[11]}, #{row[12]}, #{row[10]}, #{row[0]}, #{row[2]}, #{row[6]}, #{row[1]}, #{row[5]},'#{issued}',#{duration},#{row[13]},'#{reported}','#{issued + duration.to_i}')"
      end
      ActiveRecord::Base.connection.insert("INSERT INTO offers(id,sell,region_id,solarsystem_id,station_id,price,item_id,volume_min,volume_remain,volume_enter,issued,duration,range,reported,expires) VALUES" + @sql.join(',') + "ON DUPLICATE KEY UPDATE volume_remain=VALUES(volume_remain), reported=VALUES(reported), price=VALUES(price);") if !@sql.empty?
      imap.store(id, "+FLAGS", [:Deleted])
#      sql.puts "INSERT INTO offers(id,region_id,solarsystem_id,station_id,price,item_id,volume_min,volume_remain,volume_enter,issued,duration,range,reported,expires) VALUES" + @sql.join(',') + "ON DUPLICATE KEY UPDATE volume_remain=VALUES(volume_remain), reported=VALUES(reported), price=VALUES(price);"
      if (@csvcount % 100) == 0
          print "#{(@csvcount / @m.length)*100}% (#{@csvcount}) mails done\n"
#        sql.flush
      end
    end
    imap.close
  end
      
    
  end

	def self.import
    logger.silence do
      #Dir.glob(File.join('/home', 'dominik','code','rails', "*.dump")).sort.each do |file|
      Dir.glob(File.join('/home','dominik', 'data',"*.dump")).sort.each do |file|
        pp file
        next if ProcessedDumps.find_by_name(File.split(file)[1])
        
        @sql = []
        @count = 0
        @csvcount = 0
        csv = CSV::Reader.parse(File.open(file))
        offers = Offer.count
        procDump = ProcessedDumps.new(:name => File.split(file)[1])
		
    	csv.each do |row|
          @csvcount += 1
#          next if @csvcount < 6000  
          next if row[0] == "orderid"
          duration = row[11].split(':')[0]
          issued = Date.parse(row[10])
    	  reported = DateTime.parse(row[14].split('.')[0]).strftime('%Y-%m-%d	%H:%M:%S')
    
     	  @sql.push "(#{row[0]}, #{row[5]}, #{row[1]}, #{row[2]}, #{row[3]}, #{row[6]}, #{row[4]}, #{row[7]}, #{row[8]}, #{row[9]},'#{issued}',#{duration},#{row[12]},'#{reported}','#{issued + duration.to_i}')"
          
          if @count == 200
		    ActiveRecord::Base.connection.insert("INSERT INTO offers(id,sell,region_id,solarsystem_id,station_id,price,item_id,volume_min,volume_remain,volume_enter,issued,duration,range,reported,expires) VALUES" + @sql.join(',') + "ON DUPLICATE KEY UPDATE volume_remain=VALUES(volume_remain), reported=VALUES(reported), price=VALUES(price);")
			print "."
            @count = 0
			@sql = []
          end
          @count += 1
        end
        ActiveRecord::Base.connection.insert("INSERT INTO offers(id,sell,region_id,solarsystem_id,station_id,price,item_id,volume_min,volume_remain,volume_enter,issued,duration,range,reported,expires) VALUES" + @sql.join(',') + "ON DUPLICATE KEY UPDATE volume_remain=VALUES(volume_remain), reported=VALUES(reported), price=VALUES(price);") if !@sql.empty?
        procDump.offers = Offer.count - offers
        procDump.save!
     end
    end
  end
end
#			rescue ActiveRecord::StatementInvalid => err
	#			update = "UPDATE prices SET price=#{row[6]}, volume_remain=#{row[8]}, reported='#{reported}'WHERE id=#{row[0]} LIMIT 1;"
		#		ActiveRecord::Base.connection.update(update)
		#	end



